package com.kifas.service.dataaccess;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.kifas.entity.BarangEntity;
import com.kifas.entity.GudangEntity;
import com.kifas.entity.StokOpnameEntity;

/**
 * Created by IntelliJ IDEA.
 * User: brainmaster
 * Date: 4/28/11
 * Time: 12:19 AM
 */
@Repository
public class StokOpnameDao {
	
	private static Logger log = Logger.getLogger(StokOpnameDao.class);

    @Autowired
    private SessionFactory sessionFactory;

    private static final String ALL_STOK = "from StokOpnameEntity so ";
    private static final String STOK_BY_ID = ALL_STOK + " where so.idStok = :idStok and so.tanggal = :yesterday " +
    		"order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc";
    private static final String STOK_EXPIRED_BY_BARANG = ALL_STOK + " where so.barang = :barang " +
    		"and cast(so.bulanKadaluarsa as int) > :bulanKadaluarsa " +
    		"and year(so.tanggal) = year(:yesterday) " +
    		"and month(so.tanggal) = month(:yesterday) " +
    		"and day(so.tanggal) = day(:yesterday) " +
    		"order by so.barang, so.satuan, so.gudang, so.bulanKadaluarsa, so.tanggal desc";
    private static final String STOK_EXPIRED = ALL_STOK + " where cast(so.bulanKadaluarsa as int) > :bulanKadaluarsa " +
    		"and year(so.tanggal) = year(:yesterday) " +
    		"and month(so.tanggal) = month(:yesterday) " +
    		"and day(so.tanggal) = day(:yesterday) " +
    		"order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc";
    private static final String STOK_BARANG = ALL_STOK + " where so.barang = :barang " +
		    "and year(so.tanggal) = year(:yesterday) " +
			"and month(so.tanggal) = month(:yesterday) " +
			"and day(so.tanggal) = day(:yesterday) " + 
			"order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc";
    private static final String STOK_GUDANG = ALL_STOK + " where so.gudang = :gudang " +
		    "and year(so.tanggal) = year(:yesterday) " +
			"and month(so.tanggal) = month(:yesterday) " +
			"and day(so.tanggal) = day(:yesterday) " +
			"order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc";
    private static final String STOK_AVAILABLE = ALL_STOK + " where so.qty > 0 " +
		    "and year(so.tanggal) = year(:yesterday) " +
			"and month(so.tanggal) = month(:yesterday) " +
			"and day(so.tanggal) = day(:yesterday) " +
			"order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc";
    private static final String STOK_AVAILABLE_BY_BARANG = ALL_STOK + " where so.barang = :barang and so.qty > 0 " +
		    "and year(so.tanggal) = year(:yesterday) " +
			"and month(so.tanggal) = month(:yesterday) " +
			"and day(so.tanggal) = day(:yesterday) " + 
			"order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc";

    private static final String STOK_AVAILABLE_BY_GUDANG = ALL_STOK + " where so.gudang = :gudang and so.qty > 0 " +
		    "and year(so.tanggal) = year(:yesterday) " +
			"and month(so.tanggal) = month(:yesterday) " +
			"and day(so.tanggal) = day(:yesterday) " +
			"order by so.gudang, so.barang, so.bulanKadaluarsa, so.tanggal desc";
    

    @SuppressWarnings("unchecked")
    public List<StokOpnameEntity> getStokOpnames() {
        return filterLatestStok(sessionFactory.getCurrentSession()
                .createQuery(ALL_STOK + "order by so.barang, so.gudang, so.bulanKadaluarsa, so.tanggal desc")
                .list());
    }

    public StokOpnameEntity getStokOpname(Long idStok) {
        return (StokOpnameEntity) sessionFactory.getCurrentSession()
                .createQuery(STOK_BY_ID)
                .setLong("idStok",idStok)
                .setDate("yesterday", getYesterday())
                .uniqueResult();
    }
    
    @SuppressWarnings("unchecked")
	public List<StokOpnameEntity> getStokOpnameByBarang(BarangEntity barangEntity) {
    	return filterLatestStok(sessionFactory.getCurrentSession()
    			.createQuery(STOK_BARANG)
    			.setEntity("barang", barangEntity)
    			.setDate("yesterday", getYesterday())
    			.list());
    }

    @SuppressWarnings("unchecked")
	public List<StokOpnameEntity> getStokOpnameByGudang(GudangEntity gudangEntity) {
    	return filterLatestStok(sessionFactory.getCurrentSession()
    			.createQuery(STOK_GUDANG)
    			.setEntity("gudang", gudangEntity)
    			.setDate("yesterday", getYesterday())
    			.list());
    }
    
    @SuppressWarnings("unchecked")
	public List<StokOpnameEntity> getStokOpnameAvailable(){
    	return filterLatestStok(sessionFactory.getCurrentSession()
    			.createQuery(STOK_AVAILABLE)
    			.setDate("yesterday", getYesterday())
    			.list());
    }
    
    @SuppressWarnings("unchecked")
	public List<StokOpnameEntity> getStokOpnameAvailable(BarangEntity barangEntity) {
    	return filterLatestStok(sessionFactory.getCurrentSession()
    			.createQuery(STOK_AVAILABLE_BY_BARANG)
    			.setEntity("barang", barangEntity)
    			.setDate("yesterday", getYesterday())
    			.list());
    }

    @SuppressWarnings("unchecked")
	public List<StokOpnameEntity> getStokOpnameAvailable(GudangEntity gudangEntity) {
    	return filterLatestStok(sessionFactory.getCurrentSession()
    			.createQuery(STOK_AVAILABLE_BY_GUDANG)
    			.setEntity("gudang", gudangEntity)
    			.setDate("yesterday", getYesterday())
    			.list());
    }

    @SuppressWarnings("unchecked")
    public List<StokOpnameEntity> getExpiredStokByBarang(BarangEntity barangEntity) {
    	GregorianCalendar calendar = new GregorianCalendar();
    	SimpleDateFormat format = new SimpleDateFormat("yyyyMM");
        return filterLatestStok(sessionFactory.getCurrentSession()
                .createQuery(STOK_EXPIRED_BY_BARANG)
                .setInteger("bulanKadaluarsa", Integer.valueOf(format.format(calendar.getTime())))
                .setEntity("barang",barangEntity)
                .setDate("yesterday", getYesterday())
                .list());
    }

    @SuppressWarnings("unchecked")
	public List<StokOpnameEntity> getExpiredStok() {
    	GregorianCalendar calendar = new GregorianCalendar();
    	SimpleDateFormat format = new SimpleDateFormat("yyyyMM");
    	log.debug("bulanKadaluarsa : "+ Integer.valueOf(format.format(calendar.getTime())));
    	log.debug("yesteday : "+ getYesterday());
        return filterLatestStok(sessionFactory.getCurrentSession()
                .createQuery(STOK_EXPIRED)
                .setInteger("bulanKadaluarsa", Integer.valueOf(format.format(calendar.getTime())))
                .setDate("yesterday", getYesterday())
                .list());
    }
    
    public void insert(StokOpnameEntity stokOpnameEntity) throws Exception {
        sessionFactory.getCurrentSession().save(stokOpnameEntity);
    }
    
    private List<StokOpnameEntity> filterLatestStok(List<StokOpnameEntity> oldStoks) {
    	if(oldStoks.size() == 0) return oldStoks;
    	List<StokOpnameEntity> stoks = new ArrayList<StokOpnameEntity>();
    	if(oldStoks.size() == 1) {
    		stoks.add(oldStoks.get(0));
    		return stoks;
    	}
    	boolean exist = false;
    	for(StokOpnameEntity stok : oldStoks) {
    		if(stoks.size() == 0) {
    			stoks.add(stok);
    		} else {
    			for(StokOpnameEntity tempStok : stoks){
        			if(stok.getBarang().getIdBarang() == tempStok.getBarang().getIdBarang() && 
            				stok.getGudang().getIdGudang() == tempStok.getGudang().getIdGudang() &&
            				stok.getBulanKadaluarsa().equals(tempStok.getBulanKadaluarsa())) {
        				exist = true;
        			}
        			
        		}
    			if(!exist) {
	    			stoks.add(stok);
	    		}
    			exist = false;
    		}
    	}
		return stoks;
	}
    
    protected Date getYesterday() {
    	Calendar calendar = new GregorianCalendar();
    	calendar.add(Calendar.DATE, -1);
    	return calendar.getTime();
    }

}
